﻿Public Class MASCLINIC
    Private CLINIC = Nothing
    Private CLINICCODE = Nothing
    Private CLINICNAME = Nothing
    Private CLINICNAME_EN = Nothing
    Private TEL As String
    Private COLOR = Nothing
    Private COPERATEID = Nothing
    Private STKID = Nothing
    Private PHX = Nothing
    Private CASH = Nothing
    Private DR = Nothing
    Private DC = Nothing
    Private XRY = Nothing
    Private LAB = Nothing
    Private FMED = Nothing
    Private F_SEQ = Nothing
    Private F_WRD = Nothing
    Private F_EXM = Nothing
    Private ClinicStatus = Nothing

    'Private selectMasprename = "SELECT PRENAME, FTPRENAME, FEPRENAME, STPRENAME, SEPRENAME, SEX, convert(bit, [STATUS]) FROM MASPRENAME"
    Private selectMasClinic As String = "SELECT mc.`clinic` AS 'รหัสหน่วยงาน', mc.`cliniccode` AS 'ชื่อย่อหน่วยงาน', mc.`clinicname` AS 'ชื่อหน่วยงาน', mc.`clinicname_en`, mc.`tel` AS 'เบอร์โทร', mc.`color` AS 'COLOR', mco.`coperateid`, mco.`coperatename` AS 'หมวดหัตถการ', mc.`stkid` AS 'รหัสคลัง', stk.`stkname` AS 'คลัง', mc.`phx` AS 'รหัสคลินิกยาที่ส่งต่อ', mc2.clinicname AS 'คลินิกยาที่ส่งต่อ', mc.`cash` AS 'รหัสแผนกการเงินที่ส่งต่อ', mc3.clinicname AS 'แผนกการเงินที่ส่งต่อ', mc.`dr` AS 'รหัสห้องตรวจที่ส่งต่อ', mc4.`clinicname` AS 'ชื่อห้องตรวจที่ส่งต่อ', mc.`dc` AS 'รหัสแผนกส่งต่ออัตโนมัติ', mc5.`clinicname` AS 'แผนกที่ส่งต่อโดยอัตโนมัติ', mc.`xry` AS 'รหัสแผนกxrayที่ส่งต่อ', mc6.`clinicname` AS 'ชื่อแผนกxrayที่ส่งต่อ', mc.`lab` AS 'รหัสแผนกlabที่ส่งต่อ', mc7.`clinicname` AS 'ชื่อแผนกlabที่ส่งต่อ', mc.`fmed` AS 'fmeddgv', mc.`f_seq` AS 'F_SEQdgv', mc.`f_wrd` AS 'F_WRDdgv', mc.`f_exm` AS 'F_EXMdgv', mc.`status` AS 'สถานะการเปิดใช้งาน' FROM masclinic mc LEFT JOIN (SELECT * FROM masclsoperate WHERE `status` = 1) AS mco ON mc.`coperateid` = mco.`coperateid` LEFT JOIN stock stk ON mc.stkid = stk.stkid LEFT JOIN masclinic mc2 ON mc.phx = mc2.clinic LEFT JOIN masclinic mc3 ON mc.cash = mc3.clinic LEFT JOIN masclinic mc4 ON mc.dr = mc4.clinic LEFT JOIN masclinic mc5 ON mc.dc = mc5.clinic LEFT JOIN masclinic mc6 ON mc.xry = mc6.clinic LEFT JOIN masclinic mc7 ON mc.lab = mc7.clinic ORDER BY mc.`clinic` ASC;"
    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection

    Property CLINIC_() As Integer
        Get
            Return CLINIC
        End Get
        Set(value As Integer)
            CLINIC = value
        End Set
    End Property

    Property CLINICCODE_() As String
        Get
            Return CLINICCODE
        End Get
        Set(value As String)
            CLINICCODE = value
        End Set
    End Property

    Property CLINICNAME_() As String
        Get
            Return CLINICNAME
        End Get
        Set(value As String)
            CLINICNAME = value
        End Set
    End Property

    Property CLINICNAME_EN_() As String
        Get
            Return CLINICNAME_EN
        End Get
        Set(value As String)
            CLINICNAME_EN = value
        End Set
    End Property

    Property FMED_() As Boolean
        Get
            Return FMED
        End Get
        Set(value As Boolean)
            If value = True Then
                FMED = 1
            Else
                FMED = 0
            End If
        End Set
    End Property

    Property TEL_() As String
        Get
            Return TEL
        End Get
        Set(value As String)
            TEL = value
        End Set
    End Property

    Property COLOR_() As String
        Get
            Return COLOR
        End Get
        Set(value As String)
            COLOR = value
        End Set
    End Property

    Property COPERATEID_() As Integer
        Get
            Return COPERATEID
        End Get
        Set(value As Integer)
            COPERATEID = value
        End Set
    End Property

    Property STKID_() As Integer
        Get
            Return STKID
        End Get
        Set(value As Integer)
            STKID = value
        End Set
    End Property

    Property PHX_() As Integer
        Get
            Return PHX
        End Get
        Set(value As Integer)
            PHX = value
        End Set
    End Property

    Property CASH_() As Integer
        Get
            Return CASH
        End Get
        Set(value As Integer)
            CASH = value
        End Set
    End Property

    Property DR_() As Integer
        Get
            Return DR
        End Get
        Set(value As Integer)
            DR = value
        End Set
    End Property

    Property DC_() As Integer
        Get
            Return DC
        End Get
        Set(value As Integer)
            DC = value
        End Set
    End Property

    Property XRY_() As Integer
        Get
            Return XRY
        End Get
        Set(value As Integer)
            XRY = value
        End Set
    End Property

    Property LAB_() As Integer
        Get
            Return LAB
        End Get
        Set(value As Integer)
            LAB = value
        End Set
    End Property

    Property F_WRD_() As Boolean
        Get
            Return F_WRD
        End Get
        Set(value As Boolean)
            If value = True Then
                F_WRD = 1
            Else
                F_WRD = 0
            End If
        End Set
    End Property

    Property F_SEQ_() As Boolean
        Get
            Return F_SEQ
        End Get
        Set(value As Boolean)
            If value = True Then
                F_SEQ = 1
            Else
                F_SEQ = 0
            End If
        End Set
    End Property

    Property F_EXM_() As Boolean
        Get
            Return F_EXM
        End Get
        Set(value As Boolean)
            If value = True Then
                F_EXM = 1
            Else
                F_EXM = 0
            End If
        End Set
    End Property

    Property ClinicStatus_() As Boolean
        Get
            Return ClinicStatus
        End Get
        Set(value As Boolean)
            If value = True Then
                ClinicStatus = 1
            Else
                ClinicStatus = 0
            End If
        End Set
    End Property

    Property selectMasClinic_() As String
        Get
            Return selectMasClinic
        End Get
        Set(value As String)

        End Set
    End Property

    Public Function insertMasClinicData() As Boolean
        If TypeOf CLINICCODE Is String Then
            If connect.GetTable("SELECT `cliniccode` FROM masclinic WHERE `cliniccode` = '" & CLINICCODE & "'").Rows.Count > 0 Then
                MsgBox("มีข้อมูลส่วนนี้อยู่ในระบบแล้ว", MsgBoxStyle.Critical)
                Return False
            Else
                Try
                    connect = CONDB.NewConnection()
                    connect.BeginTrans()

                    Dim sql1 As String = ""
                    Dim sql2 As String = ""
                    Dim sql3 As String = ""

                    If CLINICCODE IsNot Nothing Then
                        sql2 += "cliniccode, "
                        sql3 += "'" & CLINICCODE & "', "
                    Else
                        sql2 += "cliniccode, "
                        sql3 += "default, "
                    End If

                    If CLINICNAME IsNot Nothing Then
                        sql2 += "clinicname, "
                        sql3 += "'" & CLINICNAME & "', "
                    Else
                        sql2 += "clinicname, "
                        sql3 += "default, "
                    End If

                    If CLINICNAME_EN IsNot Nothing Then
                        sql2 += "clinicname_en, "
                        sql3 += "'" & CLINICNAME_EN & "', "
                    Else
                        sql2 += "clinicname_en, "
                        sql3 += "default, "
                    End If

                    If TEL IsNot Nothing Then
                        sql2 += "tel, "
                        sql3 += "'" & TEL & "', "
                    Else
                        sql2 += "tel, "
                        sql3 += "default, "
                    End If

                    If COLOR IsNot Nothing Then
                        sql2 += "color, "
                        sql3 += "'" & COLOR & "', "
                    Else
                        sql2 += "color, "
                        sql3 += "null, "
                    End If

                    If COPERATEID IsNot Nothing Then
                        sql2 += "coperateid, "
                        sql3 += "'" & COPERATEID & "', "
                    Else
                        sql2 += "coperateid, "
                        sql3 += "default, "
                    End If

                    If STKID IsNot Nothing Then
                        sql2 += "stkid, "
                        sql3 += "'" & STKID & "', "
                    Else
                        sql2 += "stkid, "
                        sql3 += "default, "
                    End If

                    If PHX IsNot Nothing Then
                        sql2 += "phx, "
                        sql3 += "'" & PHX & "', "
                    Else
                        sql2 += "phx, "
                        sql3 += "default, "
                    End If

                    If CASH IsNot Nothing Then
                        sql2 += "cash, "
                        sql3 += "'" & CASH & "', "
                    Else
                        sql2 += "cash, "
                        sql3 += "default, "
                    End If

                    If DR IsNot Nothing Then
                        sql2 += "dr, "
                        sql3 += "'" & DR & "', "
                    Else
                        sql2 += "dr, "
                        sql3 += "default, "
                    End If

                    If DC IsNot Nothing Then
                        sql2 += "dc, "
                        sql3 += "'" & DC & "', "
                    Else
                        sql2 += "dc, "
                        sql3 += "default, "
                    End If

                    If XRY IsNot Nothing Then
                        sql2 += "xry, "
                        sql3 += "'" & XRY & "', "
                    Else
                        sql2 += "xry, "
                        sql3 += "default, "
                    End If

                    If LAB IsNot Nothing Then
                        sql2 += "lab, "
                        sql3 += "'" & LAB & "', "
                    Else
                        sql2 += "lab, "
                        sql3 += "default, "
                    End If

                    sql2 += "fmed, "
                    sql3 += "'" & FMED & "', "

                    sql2 += "f_seq, "
                    sql3 += "'" & F_SEQ & "', "

                    sql2 += "f_wrd, "
                    sql3 += "'" & F_WRD & "', "

                    sql2 += "f_exm, "
                    sql3 += "'" & F_EXM & "', "

                    sql2 += "status"
                    sql3 += "'" & ClinicStatus & "'"

                    sql1 += "INSERT INTO masclinic (" & sql2 & ") VALUES (" & sql3 & ");"

                    connect.ExecuteNonQuery(sql1)
                    connect.CommitTrans()
                    MsgBox("ท่านได้ทำการเพิ่มข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                    Return True
                Catch ex As Exception
                    connect.RollbackTrans()
                    MsgBox(ex.ToString)
                    Return False
                Finally
                    connect.Dispose()
                End Try
            End If
        Else
            MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร", MsgBoxStyle.Critical)
            Return False
        End If
    End Function

    Public Function editMasClinicData() As Boolean
        If TypeOf CLINICCODE Is String Then
            connect = CONDB.NewConnection()
            connect.BeginTrans()
            Try

                Dim sql As String = ""
                sql += "UPDATE masclinic SET "

                sql += "cliniccode = '" & CLINICCODE & "', "
                sql += "clinicname = '" & CLINICNAME & "', "
                sql += "clinicname_EN = '" & CLINICNAME_EN & "', "
                sql += "tel = '" & TEL & "', "

                If Convert.ToString(COLOR) <> "" Or COLOR IsNot Nothing Then
                    sql += "color = '" & COLOR & "', "
                Else
                    sql += "color = null, "
                End If

                If Convert.ToString(COPERATEID) <> "" Or COLOR IsNot Nothing Then
                    sql += "coperateid = '" & COPERATEID & "', "
                Else
                    sql += "coperateid = null, "
                End If

                If Convert.ToString(STKID) <> "" Or COLOR IsNot Nothing Then
                    sql += "stkid = '" & STKID & "', "
                Else
                    sql += "stkid = null, "
                End If

                If Convert.ToString(PHX) <> "" Or PHX IsNot Nothing Then
                    sql += "phx = '" & PHX & "', "
                Else
                    sql += "phx = null, "
                End If

                If Convert.ToString(CASH) <> "" Or CASH IsNot Nothing Then
                    sql += "cash = '" & CASH & "', "
                Else
                    sql += "cash = null, "
                End If

                If Convert.ToString(DR) <> "" Or DR IsNot Nothing Then
                    sql += "dr = '" & DR & "', "
                Else
                    sql += "dr = null, "
                End If

                If Convert.ToString(DC) <> "" Or DC IsNot Nothing Then
                    sql += "dc = '" & DC & "', "
                Else
                    sql += "dc = null, "
                End If

                If Convert.ToString(XRY) <> "" Or XRY IsNot Nothing Then
                    sql += "xry = '" & XRY & "', "
                Else
                    sql += "xry = null, "
                End If

                If Convert.ToString(LAB) <> "" Or LAB IsNot Nothing Then
                    sql += "lab = '" & LAB & "', "
                Else
                    sql += "lab = null, "
                End If

                sql += "fmed = '" & FMED & "', "
                sql += "f_seq = '" & F_SEQ & "', "
                sql += "f_wrd = '" & F_WRD & "', "
                sql += "f_exm = '" & F_EXM & "', "
                sql += "status = '" & ClinicStatus & "' "

                sql += "WHERE clinic = '" & CLINIC & "'"

                connect.ExecuteNonQuery(sql)
                connect.CommitTrans()
                MsgBox("ท่านได้ทำการเพิ่มข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
                Return False
            Finally
                connect.Dispose()
            End Try
        Else
            MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            Return False
        End If
    End Function
End Class
